package cn.topevery.garden.dal.dao.impl;

import cn.hutool.core.collection.CollUtil;
import cn.topevery.garden.dal.dao.GreFileDao;
import cn.topevery.garden.dal.dao.GreLandDao;
import cn.topevery.garden.dal.dao.GreLandRecordDao;
import cn.topevery.garden.entity.po.GreFile;
import cn.topevery.garden.entity.po.GreLand;
import cn.topevery.garden.entity.po.GreLandRecord;
import cn.topevery.garden.entity.query.GreLandQuery;
import cn.topevery.garden.entity.vo.*;
import cn.topevery.garden.framework.base.BaseDaoImpl;
import hw.topevery.framework.GeometryHelper;
import hw.topevery.framework.db.CommandType;
import hw.topevery.framework.db.DbCommand;
import hw.topevery.framework.db.DbScriptHelper;
import hw.topevery.framework.db.entity.DataPager;
import hw.topevery.framework.db.entity.SqlQueryMate;
import hw.topevery.framework.db.entity.SqlUpdateMate;
import hw.topevery.framework.db.enums.ScriptConditionEnum;
import hw.topevery.framework.dto.PointDto;
import hw.topevery.framework.entity.DbCsp;
import hw.topevery.framework.entity.KeyValue;
import hw.topevery.framework.entity.Value;
import hw.topevery.framework.enums.GeometryTypeEnum;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.locationtech.jts.geom.Geometry;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.temporal.TemporalAdjusters;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.stream.Collectors;

/**
 * @author code-generate
 * @date 2022-01-05
 */
@Repository
public class GreLandDaoImpl extends BaseDaoImpl<GreLand> implements GreLandDao {

    @Autowired
    GreFileDao greFileDao;

    @Autowired
    GreLandRecordDao greLandRecordDao;

    @Override
    public <P extends DbCsp> List<GreLand> getSearch(P para) {
        return null;
    }


    @Override
    public DataPager<GreLandVO> listPage(GreLandQuery query) {
//        String table = "t_gre_land t\n" +
//                "        left join t_sys_class tsc on t.c_type = tsc.c_class_id";
//
//        String columns = "t.*,\n" +
//                "        tsc.c_name as landType";
//
        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;
//
//        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
//        sqlQueryMate
//                .where(true, "t.c_db_status", ScriptConditionEnum.Eq, 0)
//                .where(StringUtils.isNotBlank(query.getCode()), "t.c_code", ScriptConditionEnum.Like, query.getCode())
//                .where(StringUtils.isNotBlank(query.getGreeClass()), "t.c_gree_class", ScriptConditionEnum.Like, query.getGreeClass())
//                .where(StringUtils.isNotBlank(query.getBigClassId()), "t.c_big_class_id", ScriptConditionEnum.Like, query.getBigClassId() )
//                .where(StringUtils.isNotBlank(query.getAloneLoad()), "t.t.c_alone_load", ScriptConditionEnum.Like,query.getAloneLoad()  )
//                .where(StringUtils.isNotBlank(query.getType()), "concat(ifnull(`t`.`c_big_class_id`,''),'|',ifnull(`t`.`c_medium_class_id`,''),'|',ifnull(`t`.`c_small_class_id`,''))",
//                        ScriptConditionEnum.Like, query.getType())
//                .where(StringUtils.isNotBlank(query.getMediumClassId()), "t.c_medium_class_id", ScriptConditionEnum.Eq, query.getMediumClassId())
//                .where(StringUtils.isNotBlank(query.getSmallClassId()), "t.c_small_class_id", ScriptConditionEnum.Eq, query.getSmallClassId())
//                .where(StringUtils.isNotBlank(query.getStreetId()), "t.c_street_id", ScriptConditionEnum.Eq, query.getStreetId())
//                .where(StringUtils.isNotBlank(query.getDistractId()), "t.c_distract_id", ScriptConditionEnum.Eq, query.getDistractId())
//        ;
//        String sql = sqlQueryMate.getSql();
//        if (StringUtils.isNotBlank(query.getKeywords())) {
//            sql += " or t.c_code like %" + query.getKeywords() + "%" +
//                    " or t.c_gree_class like %" + query.getKeywords() + "%" +
//                    " or t.c_variety like %" + query.getKeywords() + "%";
//        }
//        DataPager<GreLandVO> dataPager = getDataPager(sqlQueryMate, pageSize, pageIndex, GreLandVO.class);
        StringBuilder sqlBuilder = new StringBuilder(
                " t.c_db_status = 0 "
        );
        if (StringUtils.isNotBlank(query.getCode())) {
            sqlBuilder.append(" and t.c_code = '" + query.getCode() + "'");
        }
        if (StringUtils.isNotBlank(query.getGreeClass())) {
            sqlBuilder.append(" and t.c_gree_class = '" + query.getGreeClass() + "'");
        }
        if (StringUtils.isNotBlank(query.getBigClassId())) {
            sqlBuilder.append(" and t.c_big_class_id = '" + query.getBigClassId() + "'");
        }
        if (StringUtils.isNotBlank(query.getAloneLoad())) {
            sqlBuilder.append(" and t.c_alone_load = '" + query.getAloneLoad() + "'");
        }
        if (StringUtils.isNotBlank(query.getType())) {
            sqlBuilder.append(" and concat(ifnull(`t`.`c_big_class_id`,''),'|',ifnull(`t`.`c_medium_class_id`,''),'|',ifnull(`t`.`c_small_class_id`,'')) like  concat ('%','" + query.getType() + "','%')");
        }
        if (StringUtils.isNotBlank(query.getMediumClassId())) {
            sqlBuilder.append(" and t.c_medium_class_id = ‘" + query.getMediumClassId() + "'");
        }
        if (StringUtils.isNotBlank(query.getSmallClassId())) {
            sqlBuilder.append(" and t.c_small_class_id = '" + query.getSmallClassId() + "'");
        }
        if (StringUtils.isNotBlank(query.getStreetId())) {
            sqlBuilder.append(" and t.c_street_id = '" + query.getStreetId() + "'");
        }
        if (StringUtils.isNotBlank(query.getDistractId())) {
            sqlBuilder.append(" and t.c_distract_id = '" + query.getDistractId() + "'");
        }
        if (StringUtils.isNotBlank(query.getKeywords())) {
            sqlBuilder.append(" and ( t.c_code like concat ('%','" + query.getKeywords() + "','%')" +
                    " or t.c_medium_class_name like concat ('%','" + query.getKeywords() + "','%')" +
                    " or t.c_big_class_name like concat ('%','" + query.getKeywords() + "','%') )");
        }
        if(ObjectUtils.isNotEmpty(query.getMapRangesPoints())){
            sqlBuilder.append(" and ST_Distance(c_map_ranges,ST_GeomFromText('MULTIPOLYGON(");
            for(int i=0;i<query.getMapRangesPoints().size();i++){
                List<PointDto> points = query.getMapRangesPoints().get(i);
                if(i>0){
                    sqlBuilder.append(",");
                }
                sqlBuilder.append("((");
                for(int j=0;j<points.size();j++){
                    PointDto point = points.get(j);
                    if(j>0){
                        sqlBuilder.append(",");
                    }
                    sqlBuilder.append(point.getLng()+ " "+ point.getLat());
                }
                sqlBuilder.append("))");
            }
            sqlBuilder.append(")') )=0");
        }
        String sqlPage = DbScriptHelper.getSelectSqlPage("t_gre_land t left join t_sys_class tsc on t.c_type = tsc.c_class_id",
                "t.*, tsc.c_name landType",
                sqlBuilder.toString(), null, pageSize, pageIndex);
        DataPager<GreLandVO> dataPagerByCustomize = getDataPagerByCustomize(sqlPage, CommandType.Text, null, GreLandVO.class);
        return dataPagerByCustomize;
    }

    @Override
    public DataPager<GreLandRecordVO> getRecordList(GreLandQuery query) {
        String table = "t_gre_land_record t\n" +
                "        left join t_sys_class tsc on t.c_type = tsc.c_class_id\n" +
                "        JOIN t_sys_user tsu ON t.c_db_create_id = tsu.c_id";

        String columns = "t.*,\n" +
                "        tsc.c_name as landType,\n" +
                "        tsu.c_user_name `c_db_create_name`";

        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns).setOrderBy("t.c_db_create_date desc");
        sqlQueryMate
                .where(true, "t.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(StringUtils.isNotBlank(query.getLandId()), "t.c_land_id", ScriptConditionEnum.Eq, query.getLandId())
                .where(query.getLandIntId() != null, "t.c_land_int_id", ScriptConditionEnum.Eq, query.getLandIntId())
        ;

        DataPager<GreLandRecordVO> dataPager = getDataPager(sqlQueryMate, pageSize, pageIndex, GreLandRecordVO.class);
        return dataPager;
    }

    @Override
    public GreLand getEntityByColumn(String key, String value) {
        KeyValue<String> map = new KeyValue<>();
        map.key = key;
        map.value = value;
        return getEntityByColumnEqValue(map);
    }

    @Override
    public GreLandVO getGreLandById(Integer id) {

        Value<GreLandVO> res = new Value<>();
        List<KeyValue<?>> parameters = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT t.*,\n" +
                "        tsc.c_name as landType,\n" +
                "        tsd.c_dept_name\n" +
                "        from t_gre_land t\n" +
                "        left join t_sys_class tsc on t.c_type = tsc.c_class_id\n" +
                "        LEFT JOIN t_sys_dept tsd on t.c_dept_id = tsd.c_dept_id " +
                " where  t.c_db_status = 0 and t.c_id = " + id);
        String sqlText = sb.toString();
        getDbExecute().run(sqlText, CommandType.Text, dbCmd -> {
            res.data = dbCmd.executeToEntity(GreLandVO.class);
        });
        return res.data;
    }

    @Override
    public List<Map<String, Object>> getShortList(String deptId) {
        String sql = "select c_land_id `id`,c_gree_class `value`,c_code `code` from t_gre_land where c_db_status =0 " +
                "and c_gree_class !=''";

        if (deptId != "") {
            sql = sql + " and c_dept_id=" + deptId;
        }
        return getMap(sql);
    }

    @Override

    public void commit(GreLandVO vo, Integer userId) {
        boolean isUpdate = vo.getId() != null;
        LocalDateTime now = LocalDateTime.now();
        GreLand greLand = new GreLand();
        BeanUtils.copyProperties(vo, greLand);
        if (CollUtil.isNotEmpty(vo.getMapRangesPoints())) {
            greLand.setMapRanges(GeometryHelper.getGeometry(GeometryHelper.getWellKnownText(vo.getMapRangesPoints(), GeometryTypeEnum.PLANE)));
            greLand.setGeoWkt(GeometryHelper.getWellKnownText(vo.getMapRangesPoints(), GeometryTypeEnum.PLANE));
        }

        if (!isUpdate) {
            greLand.setDbStatus(true);
            greLand.setCreateId(userId.toString());
            greLand.setCreateTime(now);
            greLand.setLandId(UUID.randomUUID().toString());
            greLand.setObjName("6");
            saveOrUpdate(userId.toString(), greLand);
        } else {
            //如果是修改，先删除旧的附件，再把新的附件保存
            greFileDao.deleteByMainId(vo.getId(), userId.toString(), 1);
            greLand.setCreateTime(now);
            greLand.setCreateId(userId.toString());
            saveOrUpdate(userId.toString(), greLand);
        }

        //存绿地信息表
        GreLandRecord greLandRecord = new GreLandRecord();
        BeanUtils.copyProperties(greLand, greLandRecord, "id");
        greLandRecord.setLandIntId(greLand.getId());
        greLandRecordDao.saveOrUpdate(userId.toString(), greLandRecord);
        if (vo.getFileList() != null && !vo.getFileList().isEmpty()) {
            List<GreFile> collect = vo.getFileList().stream().peek(temp -> {
                temp.setDataType(1);
                temp.setCreateTime(now);
                temp.setCreateId(userId.toString());
                temp.setMainId(greLand.getId());
                temp.setMainRecordId(greLandRecord.getId());
                temp.setDbStatus(false);
            }).collect(Collectors.toList());
            for (GreFile greFile : collect) {
                greFileDao.saveOrUpdate(userId.toString(), greFile);
            }
        }
    }

    @Override
    public boolean isExist(String code) {
        boolean exist = false;
        Value<List<GreLand>> res = new Value<>();
        List<KeyValue<?>> parameters = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        sb.append("select * from c_gre_land  where c_db_status = 0 and c_code " + code);
        String sqlText = sb.toString();
        getDbExecute().run(sqlText, CommandType.Text, dbCmd -> {
            for (KeyValue parameter : parameters) {
                dbCmd.addInParameter(parameter.key, parameter.value);
            }
            res.data = dbCmd.executeToList(GreLand.class);
        });
        if (res.data != null && res.data.size() > 0) {
            exist = true;
        }
        return exist;
    }

    @Override
    public List<Map<String, Object>> getCityAreaCoveragePercent(LocalDate beginDate, LocalDate endDate, String mapId) {
        String sql = "SELECT m.greType,IFNULL(t.sumArea,0) sumArea,IFNULL(t.sumGreArea,0) sumGreArea,100 percent    FROM \n" +
                "(SELECT \n" +
                "c_big_class_name greType,\n" +
                "        sum(gre.c_measure) sumArea,\n" +
                "        sum(gre.c_measure) sumGreArea,\n" +
                "        100 percent\n" +
                "  FROM\n" +
                "        t_gre_land gre\n" +
                " where gre.c_db_status = 0";
        if (beginDate != null) {
            sql = sql + " and gre.c_date >= '" + beginDate + "'";
        }
        if (endDate != null) {
            sql = sql + " and gre.c_date <= '" + endDate + "'";
        }
        sql = sql +
                "GROUP BY  gre.c_big_class_name,gre.c_big_class_id ) t\n" +
                "RIGHT  JOIN  (SELECT c_big_class_name greType,0 sumArea,0 sumGreArea,100 percent FROM  t_gre_land GROUP BY  c_big_class_name ) m ON t.greType=m.greType\n";
        return getMap(sql);
    }

    @Override
    public List<CoveragePercentVO> getCoveragePercent() {
        LocalDate endDate = LocalDate.now();
        LocalDate beginDate = endDate.minusYears(2).with(TemporalAdjusters.firstDayOfYear());
        return getCoveragePercentByDate(beginDate, endDate);
    }

    @Override
    public Map<String, Object> getCitySumAreaCompareInfoByDate(String date) {
        String table = "t_sys_class tsc\n" +
                "        LEFT JOIN t_gre_land tgl ON tsc.c_class_id = tgl.c_big_class_id and tgl.c_db_status = 0 and tgl.c_date <=" + date;

        String columns = "SUM(\n" +
                "        IFNULL( tgl.c_measure, 0 )) sumArea,\n" +
                "        SUM(\n" +
                "        IFNULL( tgl.c_gre_measure, 0 )) sumGreArea";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true, "tsc.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(true, "tsc.c_class_pid", ScriptConditionEnum.Eq, "8c793ae3-8e14-48e7-aba6-8367cc3f0037")
        ;
        return getOneMap(sqlQueryMate);
    }

    @Override
    public Map<String, Object> getSituation() {
        String table = "(\n" +
                "\tSELECT\n" +
                "\t\tc_numerator districtGreenCoverArea,\n" +
                "\t\tc_denominator districtGreenArea,\n" +
                "\t\tc_real_value districtGreenCoverRate\n" +
                "\t\tFROM t_criterion_def tcd\n" +
                "\t\t\tLEFT JOIN t_criterion_evaluation tce ON tcd.c_criterion_id = tce.c_criterion_id\n" +
                "\t\tWHERE tce.c_map_id='f41889ad-585b-413d-878e-7040991f468d' and  tcd.c_expression = 'coreIndicators_002' and c_year=DATE_FORMAT(NOW(), '%Y')) t1,\n" +
                "\t\t(\n" +
                "\t\tSELECT\n" +
                "\t\tc_numerator cityGreenCoverArea,\n" +
                "\t\tc_denominator cityGreenArea,\n" +
                "\t\tc_real_value districtGreenRate\n" +
                "\t\tFROM t_criterion_def tcd\n" +
                "\t\t\tLEFT JOIN t_criterion_evaluation tce ON tcd.c_criterion_id = tce.c_criterion_id\n" +
                "\t\tWHERE tce.c_map_id='f41889ad-585b-413d-878e-7040991f468d' and  tcd.c_expression = 'coreIndicators_003' and c_year=DATE_FORMAT(NOW(), '%Y')) t2";

        String columns = "t2.*";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        return getOneMap(sqlQueryMate);
    }

    @Override
    public Map<String, Object> getSituationShow() {
        String table = "(\n" +
                "\tSELECT\n" +
                "\t\tc_numerator districtGreenCoverArea,\n" +
                "\t\tc_denominator districtGreenArea,\n" +
                "\t\tc_real_value districtGreenCoverRate\n" +
                "\t\tFROM t_criterion_def tcd\n" +
                "\t\t\tLEFT JOIN t_criterion_evaluation_show tce ON tcd.c_criterion_id = tce.c_criterion_id\n" +
                "\t\tWHERE tce.c_map_id='f41889ad-585b-413d-878e-7040991f468d' and  tcd.c_expression = 'coreIndicators_002' and c_year=DATE_FORMAT(NOW(), '%Y')) t1,\n" +
                "\t\t(\n" +
                "\t\tSELECT\n" +
                "\t\tc_numerator cityGreenCoverArea,\n" +
                "\t\tc_denominator cityGreenArea,\n" +
                "\t\tc_real_value districtGreenRate\n" +
                "\t\tFROM t_criterion_def tcd\n" +
                "\t\t\tLEFT JOIN t_criterion_evaluation_show tce ON tcd.c_criterion_id = tce.c_criterion_id\n" +
                "\t\tWHERE tce.c_map_id='f41889ad-585b-413d-878e-7040991f468d' and  tcd.c_expression = 'coreIndicators_003' and c_year=DATE_FORMAT(NOW(), '%Y')) t2";

        String columns = "t2.*";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        return getOneMap(sqlQueryMate);
    }

    @Override
    public Map<String, Object> getParkSituation() {
        String table = "( select\n" +
                "\tcount(1) parkCount,\n" +
                "\tifnull(ROUND(sum(a.c_measure)/10000,2),0) parkArea,\n" +
                "\tifnull(ROUND((\n" +
                "\tSELECT\n" +
                "\t\tc_numerator\n" +
                "\t\tFROM t_criterion_def tcd\n" +
                "\t\t\tLEFT JOIN t_criterion_evaluation tce ON tcd.c_criterion_id = tce.c_criterion_id\n" +
                "\t\tWHERE tce.c_map_id='f41889ad-585b-413d-878e-7040991f468d' and  tcd.c_expression = 'coreIndicators_004' and c_year=DATE_FORMAT(NOW(), '%Y')\n" +
                "\t)/10000,2),0) parkGreenArea,\n" +
                "\tifnull(ROUND((\n" +
                "\tSELECT\n" +
                "\t\tc_real_value\n" +
                "\t\tFROM t_criterion_def tcd\n" +
                "\t\t\tLEFT JOIN t_criterion_evaluation tce ON tcd.c_criterion_id = tce.c_criterion_id\n" +
                "\t\tWHERE tce.c_map_id='f41889ad-585b-413d-878e-7040991f468d' and  tcd.c_expression = 'coreIndicators_004' and c_year=DATE_FORMAT(NOW(), '%Y')\n" +
                "\t),2),0) parkGreenAreaAverage,\n" +
                "\tifnull((\n" +
                "\tSELECT\n" +
                "\t\tc_real_value\n" +
                "\t\tFROM t_criterion_def tcd\n" +
                "\t\t\tLEFT JOIN t_criterion_evaluation tce ON tcd.c_criterion_id = tce.c_criterion_id\n" +
                "\t\tWHERE tce.c_map_id='f41889ad-585b-413d-878e-7040991f468d' and  tcd.c_expression = 'coreIndicators_007' and c_year=DATE_FORMAT(NOW(), '%Y')\n" +
                "\t),0) parkGreenserviceRadiusWktCoverRate\n" +
                "From t_gre_land a join t_base_info b on 1=1 where a.c_db_status=0 and a.c_big_class_id='a46ba5d2-9048-4002-a83f-0f9096f96cf8' and b.c_map_id='f41889ad-585b-413d-878e-7040991f468d') t2";

        String columns = "t2.*";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        return getOneMap(sqlQueryMate);
    }

    @Override
    public Map<String, Object> getParkSituationShow() {
        String table = "( select\n" +
                "\tcount(1) parkCount,\n" +
                "\tifnull(ROUND(sum(a.c_measure)/10000,2),0) parkArea,\n" +
                "\tifnull(ROUND((\n" +
                "\tSELECT\n" +
                "\t\tc_numerator\n" +
                "\t\tFROM t_criterion_def tcd\n" +
                "\t\t\tLEFT JOIN t_criterion_evaluation_show tce ON tcd.c_criterion_id = tce.c_criterion_id\n" +
                "\t\tWHERE tce.c_map_id='f41889ad-585b-413d-878e-7040991f468d' and  tcd.c_expression = 'coreIndicators_004' and c_year=DATE_FORMAT(NOW(), '%Y')\n" +
                "\t)/10000,2),0) parkGreenArea,\n" +
                "\tifnull(ROUND((\n" +
                "\tSELECT\n" +
                "\t\tc_real_value\n" +
                "\t\tFROM t_criterion_def tcd\n" +
                "\t\t\tLEFT JOIN t_criterion_evaluation_show tce ON tcd.c_criterion_id = tce.c_criterion_id\n" +
                "\t\tWHERE tce.c_map_id='f41889ad-585b-413d-878e-7040991f468d' and  tcd.c_expression = 'coreIndicators_004' and c_year=DATE_FORMAT(NOW(), '%Y')\n" +
                "\t),2),0) parkGreenAreaAverage,\n" +
                "\tifnull((\n" +
                "\tSELECT\n" +
                "\t\tc_real_value\n" +
                "\t\tFROM t_criterion_def tcd\n" +
                "\t\t\tLEFT JOIN t_criterion_evaluation_show tce ON tcd.c_criterion_id = tce.c_criterion_id\n" +
                "\t\tWHERE tce.c_map_id='f41889ad-585b-413d-878e-7040991f468d' and  tcd.c_expression = 'coreIndicators_007' and c_year=DATE_FORMAT(NOW(), '%Y')\n" +
                "\t),0) parkGreenserviceRadiusWktCoverRate\n" +
                "From t_gre_land a join t_base_info b on 1=1 where a.c_db_status=0 and a.c_big_class_id='a46ba5d2-9048-4002-a83f-0f9096f96cf8' and b.c_map_id='f41889ad-585b-413d-878e-7040991f468d') t2";

        String columns = "t2.*";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        return getOneMap(sqlQueryMate);
    }

    @Override
    public List<Map<String, Object>> getAreaStat() {
        String sql = " select tsm.c_name `name`,ifnull(ROUND(sum(c_measure)/100000000,2),0) `value` From t_sys_map tsm left join t_gre_land tgl on tsm.c_map_id=tgl.c_distract_id where tsm.c_map_type='13002' group by tsm.c_name;\n";
        return getMap(sql);
    }

    @Override
    public DataPager<GreLandInDeptVO> getGreLandInDept(GreLandQuery query) {
        String table = "t_gre_land tgl join t_sys_dept\n" +
                "        tsd on tgl.c_dept_id=tsd.c_dept_id";

        String columns = "tgl.c_id,tgl.c_land_id,tgl.c_gree_class,tgl.c_dept_id,tsd.c_dept_name";

        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns).setOrderBy("tgl.c_id desc");
        sqlQueryMate
                .where(true, "tgl.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(StringUtils.isNotBlank(query.getGreeClass()), "tgl.c_gree_class", ScriptConditionEnum.LikeEq, query.getCode())
                .where(StringUtils.isNotBlank(query.getDeptName()), "tsd.c_dept_name", ScriptConditionEnum.LikeEq, query.getGreeClass())
        ;

        return getDataPager(sqlQueryMate, pageSize, pageIndex, GreLandInDeptVO.class);
    }

    @Override
    public List<CoveragePercentVO> getCoveragePercentByDate(LocalDate beginDate, LocalDate endDate) {

        Value<List<CoveragePercentVO>> res = new Value<>();
        run(" call p_get_city_area_percent_by_date('" + beginDate + "','" + endDate + "')", CommandType.Text, dbCommand -> {
            res.data = dbCommand.executeToList(CoveragePercentVO.class);
        });
        return res.data;
    }

    @Override
    public List<GreLand> getServiceAreaGreLandList() {
        SqlQueryMate sqlQueryMate = new SqlQueryMate("t_gre_land").setColumns("*");
        sqlQueryMate.where(true, "c_db_status", ScriptConditionEnum.Eq, 0)
                .where(true, "c_service_radius", ScriptConditionEnum.NotEq, null);
        return selectList(sqlQueryMate, GreLand.class);
    }

    @Override
    public DataPager<Map<String, Object>> getList(GreLandQuery query) {
        DataPager<Map<String, Object>> dataPager = new DataPager<>();
        dataPager.records = getMap("select * from t_gre_land");
        return dataPager;
    }

    @Override
    public void deleteByIds(List<String> ids, Integer userId) {
        SqlUpdateMate sqlUpdateMate = new SqlUpdateMate("t_gre_land");
        sqlUpdateMate.setColumnValue(true, "c_db_status", 1)
                .where(true, "c_id", ScriptConditionEnum.In, ids);
        run(sqlUpdateMate.getSql(), CommandType.Text, dbCommand -> {
            sqlUpdateMate.getParameters().forEach(dbCommand::addParameter);
            dbCommand.executeNonQuery();
        });
    }

    @Override
    public void updateBySql(GreLand greLand) {
        String sql = "update t_gre_land set\n" +
                "        c_land_id = " + greLand.getLandId() + ",\n" +
                "        c_code = " + greLand.getCode() + ",\n" +
                "        c_big_class_id = " + greLand.getBigClassId() + ",\n" +
                "        c_big_class_name = " + greLand.getBigClassName() + ",\n" +
                "        c_medium_class_id = " + greLand.getMediumClassId() + ",\n" +
                "        c_medium_class_name = " + greLand.getMediumClassName() + ",\n" +
                "        c_small_class_id = " + greLand.getSmallClassId() + ",\n" +
                "        c_small_class_name = " + greLand.getSmallClassName() + ",\n" +
                "        c_alone_load = " + greLand.getAloneLoad() + ",\n" +
                "        c_gree_class = " + greLand.getGreeClass() + ",\n" +
                "        c_type = " + greLand.getType() + ",\n" +
                "        c_measure = " + greLand.getMeasure() + ",\n" +
                "        c_gre_measure = " + greLand.getGreMeasure() + ",\n" +
                "        c_address= " + greLand.getAddress() + ",";
        if (StringUtils.isNotBlank(greLand.getGeoWkt())) {
            sql += "c_map_ranges = ST_GeometryFromText(" + greLand.geoWkt + "),";
        } else {
            sql += "c_map_ranges = null ,";
        }
        sql += "c_geo_wkt = " + greLand.getGeoWkt() + ",";
        if (StringUtils.isNotBlank(greLand.getServiceRadiusWkt())) {
            sql += "c_service_radius=ST_GeometryFromText(" + greLand.getServiceRadiusWkt() + "),";
        } else {
            sql += "c_service_radius = null ,";
        }
        sql += "c_street_id = " + greLand.getStreetId() + ",\n" +
                "c_street_name = " + greLand.getStreetName() + ",\n" +
                "c_distract_id = " + greLand.getDistractId() + ",\n" +
                "c_distract_name = " + greLand.getDistractName() + ",\n" +
                "c_dept_id = " + greLand.getDeptId() + ",\n" +
                "c_desc = " + greLand.getDesc() + ",\n" +
                "c_date = " + greLand.getDate() + ",\n" +
                "c_attr = " + greLand.getAttr() + ",\n" +
                "c_attr_cnt = " + greLand.getAttrCnt() + ",\n" +
                "c_is_free = " + greLand.getIsFree() + ",\n" +
                "c_db_create_id = " + greLand.getCreateId() + ",\n" +
                "c_db_create_date = " + greLand.getCreateTime() + ",\n" +
                "c_update_time=now(),\n" +
                "c_db_status = " + greLand.getDbStatus() + ",\n" +
                "c_data_public= #{greLand.dataPublic}" + ",\n" +
                "where c_id = " + greLand.getId();
        run(sql, CommandType.Text, DbCommand::executeNonQuery);
    }

    @Override
    public void dataPublics(List<Integer> ids, Integer userId) {
        ids.forEach(id -> {
            GreLandVO greLandVO = getGreLandById(id);
            greLandVO.setMapRangesPoints(GeometryHelper.getGeomePoint(greLandVO.getMapRanges()));
            greLandVO.setDataPublic(true);
            commit(greLandVO, userId);
        });
    }

    @Override
    public List<Map<String, Object>> getSurvey() {
        return getMap("select id,geom.MakeValid().STAsText() as geom_text,geom.STSrid srid\n" +
                "        from 公园_绿地");
    }

    @Override
    public void uptSurvery(String id, String geom_text) {
        SqlUpdateMate sqlUpdateMate = new SqlUpdateMate("公园绿地_bd09 a");
        sqlUpdateMate.setColumnValue(true, "a.geom_text", geom_text)
                .setColumnValue(true, "c_update_time", LocalDateTime.now())
                .where(true, "c_id", ScriptConditionEnum.Eq, id);
        run(sqlUpdateMate.getSql(), CommandType.Text, dbCommand -> {
            sqlUpdateMate.getParameters().forEach(dbCommand::addParameter);
            dbCommand.executeNonQuery();
        });
    }

    @Override
    public void updateLand(String id, String geom_text) {
        String sql = "update t_gre_land  set c_map_ranges =ST_GeometryFromText(" +
                geom_text + "), c_geo_wkt = " + geom_text + ",c_update_time=now() where c_code = " + id;
        run(sql, CommandType.Text, DbCommand::executeNonQuery);
    }

    @Override
    public List<GreLand> getNewUpdateGre(Map<String, String> map) {
        String sql = "select * From t_gre_land where c_db_status = 0 ";
        if (StringUtils.isNotBlank(map.get("bigClassId"))) {
            sql += " and c_big_class_id=" + map.get("bigClassId");
        }
        if (StringUtils.isNotBlank(map.get("interval"))) {
            sql += " and (TIMESTAMPDIFF(MINUTE,c_update_time,now()) between 0 and " + map.get("interval");
        }
        Value<List<GreLand>> result = new Value<>();
        run(sql, CommandType.Text, dbCommand -> {
            result.data = dbCommand.executeToList(GreLand.class);
        });
        return result.data;
    }

    @Override
    public void updateGreServiceArea(GreLand greLand) {
        StringBuilder sql = new StringBuilder();
        sql.append("update t_gre_land set ");
        if (StringUtils.isNotBlank(greLand.getServiceRadiusWkt())) {
            sql.append(" c_service_radius=ST_GeometryFromText( ").append(greLand.getServiceRadiusWkt());
        } else {
            sql.append(" c_service_radius=null ");
        }
        sql.append(" where c_id = ").append(greLand.getId());
        run(sql.toString(), CommandType.Text, DbCommand::executeNonQuery);
    }

    @Override
    public void insertSql(GreLandVO greLand) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO t_gre_land (\n" +
                "        c_big_class_id,\n" +
                "        c_big_class_name,\n" +
                "        c_medium_class_id,\n" +
                "        c_medium_class_name,\n" +
                "        c_small_class_id,\n" +
                "        c_small_class_name,\n" +
                "        c_distract_id,\n" +
                "        c_distract_name,\n" +
                "        c_type,\n" +
                "        c_land_id,\n" +
                "        c_code,\n" +
                "        c_gree_class,\n" +
                "        c_measure,\n" +
                "        c_gre_measure,\n" +
                "        c_address,\n" +
                "        c_date,\n" +
                "        c_db_create_id,\n" +
                "        c_db_create_date,\n" +
                "        c_db_status,\n" +
                "        c_data_public\n" +
                "        )\n" +
                "        SELECT\n" +
                "            a.c_class_id,\n" +
                "            a.c_name,\n" +
                "            b.c_class_id,\n" +
                "            b.c_name,\n" +
                "            c.c_class_id,\n" +
                "            c.c_name,\n" +
                "             t.c_map_id,\n" +
                "             t.c_name,\n" +
                "             case when c.c_name is NOT null then c.c_class_id\n" +
                "          WHEN b.c_name is NOT null THEN b.c_class_id\n" +
                "\t\t\t\telse a.c_class_id end c_type,");
        sql.append(greLand.getLandId()).append(",")
                .append(greLand.getCode()).append(",")
                .append(greLand.getGreeClass()).append(",")
                .append(greLand.getMeasure()).append(",")
                .append(greLand.getGreMeasure()).append(",")
                .append(greLand.getAddress()).append(",")
                .append(greLand.getDate()).append(",")
                .append(greLand.getDbCreateId()).append(",")
                .append(greLand.getDbCreateDate()).append(",")
                .append(greLand.getDbStatus()).append(",")
                .append(greLand.getDataPublic());
        sql.append(" FROM\n" +
                "            t_sys_class a\n" +
                "            JOIN t_sys_class b ON b.c_code = ").append(greLand.getMediumClassCode())
                .append(" JOIN t_sys_class c ON c.c_code = ").append(greLand.getSmallClassCode())
                .append(" JOIN t_sys_map t ON 1=1")
                .append(" AND t.c_name = ").append(greLand.getDistractName());
        run(sql.toString(), CommandType.Text, DbCommand::executeNonQuery);
    }

    @Override
    public String getCode(String mapId, String classId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "\tMAX(c_code)\n" +
                "FROM\n" +
                "\tt_gre_land\n" +
                "WHERE\n" +
                "c_code LIKE (\n" +
                "\tSELECT\n" +
                "\t\tCONCAT( '%',sm.c_code, sc.c_code,'%' )\n" +
                "\tFROM\n" +
                "\t\tt_sys_class sc\n" +
                "\t\tJOIN t_sys_map sm ON c_map_id = ").append(mapId)
                .append(" WHERE\n" +
                        "\t\tc_class_id = ").append(classId);
        Value<String> result = new Value<>();
        run(sql.toString(), CommandType.Text, dbCommand -> {
            result.data = dbCommand.executeToEntity(String.class);
        });
        return result.data;
    }

    @Override
    public String getNewcode(String mapId, String classId) {
        StringBuilder sql = new StringBuilder("SELECT\n" +
                "\t\tCONCAT( sm.c_code, sc.c_code )\n" +
                "\tFROM\n" +
                "\t\tt_sys_class sc\n" +
                "\t\tJOIN t_sys_map sm ON c_map_id = ").append(mapId)
                .append(" WHERE\n" +
                        "\t\tc_class_id = ").append(classId);
        Value<String> result = new Value<>();
        run(sql.toString(), CommandType.Text, dbCommand -> {
            result.data = dbCommand.executeToEntity(String.class);
        });
        return result.data;
    }

    @Override
    public List<GardenServiceRadiusVO> getGardenServiceRadius() {
        return selectList(new SqlQueryMate("v_gre_land_park_service_radius").setColumns("*"), GardenServiceRadiusVO.class);
    }

    @Override
    public List<Map<String, Object>> getLandAndParkCount() {
        return getMap("SELECT '绿地' typeName, ROUND(SUM(c_measure), 2) count, '45263435-8D81-11ED-B8DF-00155DEB0E04' typeId FROM t_gre_land WHERE c_db_status=0 \n" +
                "UNION SELECT '公园' typeName, count(*) count, 'B6D1197C-8D81-11ED-B8DF-00155DEB0E04' typeId FROM t_park WHERE c_db_status=0");
    }

    @Override
    public Geometry getGeoBuffer(String geomWellKnownText, double distance) {
        String sql = "SELECT st_buffer ( ST_GeomFromText('" + geomWellKnownText + "'), " + distance + " )  c_map_ranges";
        Value<GreLand> res = new Value<>();
        run(sql, CommandType.Text, dbCmd -> {
            res.data = dbCmd.executeToEntity(GreLand.class);
        });
        return res.data.mapRanges;
    }

    @Override
    public List<Geometry> getAllGeoRadius() {
        SqlQueryMate sqlQueryMate = new SqlQueryMate("v_park_radius").setColumns("c_geom_radius AS c_map_ranges");
        List<GreLand> geometries = selectList(sqlQueryMate, GreLand.class);
        return geometries.stream().map(GreLand::getMapRanges).collect(Collectors.toList());
    }

    public Map getCount(String name, String sectionId) {
        String table = "t_gre_land tp";

        String columns = "count(1) value,'绿地' as name";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true, "tp.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(name != null, "tp.c_big_class_name", ScriptConditionEnum.Eq, name)
                .where(StringUtils.isNotBlank(sectionId), "tp.c_obj_name", ScriptConditionEnum.Eq, sectionId)

        ;
        return getOneMap(sqlQueryMate);

    }

    public List<GreLandVO> getTypeList(String sectionId) {
        String table = "t_gre_land tp";

        String columns = "distinct tp.c_big_class_id c_type, tp.c_big_class_name c_type_name ";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true, "tp.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(StringUtils.isNotBlank(sectionId), "tp.c_obj_name", ScriptConditionEnum.Eq, sectionId)
        ;

        List<GreLandVO> list = selectList(sqlQueryMate, GreLandVO.class);
        for (GreLandVO greLand : list) {
            if (StringUtils.isNotEmpty(greLand.getType())) {
                Map map = getCount(greLand.getTypeName(), null);
                greLand.setTotalNumber(String.valueOf(map.get("value")));
            }
        }
        return list;
    }

    @Override
    public Map<String, Object> getDataByBoundResource(GreLandQuery query) {
        StringBuilder sqlBuilder = new StringBuilder();
        StringBuilder sqlBuilder1 = new StringBuilder();
        if(ObjectUtils.isNotEmpty(query.getMapRangesPoints())){
            sqlBuilder.append("ST_GeomFromText('MULTIPOLYGON(");
            sqlBuilder1.append("ST_GeomFromText('MULTIPOLYGON(");
            for(int i=0;i<query.getMapRangesPoints().size();i++){
                List<PointDto> points = query.getMapRangesPoints().get(i);
                if(i>0){
                    sqlBuilder.append(",");
                    sqlBuilder1.append(",");
                }
                sqlBuilder.append("((");
                sqlBuilder1.append("((");
                for(int j=0;j<points.size();j++){
                    PointDto point = points.get(j);
                    if(j>0){
                        sqlBuilder.append(",");
                        sqlBuilder1.append(",");
                    }
                    sqlBuilder.append(point.getLng()+ " "+ point.getLat());
                    sqlBuilder1.append(point.getLng()+ " "+ point.getLat());
                }
                sqlBuilder.append("))");
                sqlBuilder1.append("))");
            }
            sqlBuilder.append(")', 4490, 'axis-order=long-lat') ");
            sqlBuilder1.append(")')");
        }

        String table = "t_gre_land tp";

        String columns = "sum(tp.c_gre_measure) c_gre_measure,sum(tp.c_measure) c_measure,ST_Area("+sqlBuilder.toString()+") c_bound_measure";

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate
                .where(true, "tp.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(ObjectUtils.isNotEmpty(query.getMapRangesPoints()), "ST_Distance(c_map_ranges,"+sqlBuilder1.toString()+")", ScriptConditionEnum.Eq, 0)
        ;
        return getOneMap(sqlQueryMate);
    }

}
